﻿CREATE PROCEDURE [SSIS].[CreateProjectEnvironmentVariables](
	@SolutionFolderName sysname,
	@ProjectName sysname,
	@EnvironmentName sysname
)
AS
BEGIN
	--get all project parameters
	DECLARE @ProjectObjectType tinyint = 20;

	DECLARE C_PROJECT_PARAMETERS CURSOR FAST_FORWARD FOR(
		SELECT
			par.[object_name] AS 'ObjectName',
			par.[parameter_name] AS 'ParameterName',
			par.[description] AS 'ParameterDescription',
			par.[sensitive] AS 'ParameterSensitivity',
			par.[data_type] AS 'DataType',
			COALESCE(par.[default_value],par.[design_default_value],N'') AS 'DefaultValue'
		FROM [$(SSISDB)].[catalog].[object_parameters] AS par
		INNER JOIN [$(SSISDB)].[catalog].[projects] AS prj
			ON (prj.[project_id] = par.[project_id])
			AND(prj.[name] = @ProjectName)
		INNER JOIN [$(SSISDB)].[catalog].[folders] AS fld
			ON (fld.[folder_id] = prj.[folder_id])
			AND(fld.[name] = @SolutionFolderName)
		WHERE  (par.[object_type] = @ProjectObjectType)
			AND(par.[required] = 1)
	);
	DECLARE @ObjectName sysname, @ParameterName sysname, @ParameterDescription varchar(1024), @ParameterSensitivity bit, @DataType sysname, @DefaultValue sql_variant;
	OPEN C_PROJECT_PARAMETERS;
	FETCH NEXT FROM C_PROJECT_PARAMETERS INTO @ObjectName, @ParameterName, @ParameterDescription, @ParameterSensitivity, @DataType, @DefaultValue;
	WHILE(@@FETCH_STATUS = 0)
	BEGIN
		--PRINT '@ObjectName: ' + CAST(@ObjectName AS nvarchar(max))
		--PRINT '@ParameterName: ' + CAST(@ParameterName AS nvarchar(max))
		--PRINT '@ParameterDescription: ' + CAST(@ParameterDescription AS nvarchar(max))
		--PRINT '@ParameterSensitivity: ' + CAST(@ParameterSensitivity AS nvarchar(max))
		--PRINT '@DataType: ' + CAST(@DataType AS nvarchar(max))
		--PRINT '@DefaultValue: ' + CAST(@DefaultValue AS nvarchar(max))

		--check if the environment variable exists
		IF NOT EXISTS(
			SELECT * FROM [$(SSISDB)].[catalog].[environment_variables] AS var
			INNER JOIN [$(SSISDB)].[catalog].[environments] AS env
				ON (env.[environment_id] = var.[environment_id])
				AND(env.[name] = @EnvironmentName)
			INNER JOIN [$(SSISDB)].[catalog].[folders] AS fld
				ON (fld.[folder_id] = env.[folder_id])
				AND(fld.[name] = @SolutionFolderName)
			WHERE  (var.[name] = @ParameterName)
				AND(var.[type] = @DataType)
		)
			BEGIN
				--add the environment variable
				PRINT 'Adding environment variable for ' + @ParameterName;

				EXEC [$(SSISDB)].[catalog].[create_environment_variable]
					@variable_name = @ParameterName,
					@sensitive = @ParameterSensitivity,
					@description = @ParameterDescription,
					@environment_name = @EnvironmentName,
					@folder_name = @SolutionFolderName,
					@value = @DefaultValue,
					@data_type = @DataType;
			END

		--map the parameter to the environment variable
		DECLARE @RelativeReferenceType char(1) = 'R';

		PRINT 'Mapping SSIS catalog environment variable for ' + @ParameterName;
		EXEC [$(SSISDB)].[catalog].[set_object_parameter_value]
			@object_type = @ProjectObjectType,
			@parameter_name = @ParameterName,
			@object_name = @ObjectName,
			@folder_name = @SolutionFolderName,
			@project_name = @ProjectName,
			@value_type = @RelativeReferenceType,
			@parameter_value = @ParameterName;

		FETCH NEXT FROM C_PROJECT_PARAMETERS INTO @ObjectName, @ParameterName, @ParameterDescription, @ParameterSensitivity, @DataType, @DefaultValue;
	END
	CLOSE C_PROJECT_PARAMETERS;
	DEALLOCATE C_PROJECT_PARAMETERS;

	RETURN(0);
END
GO
EXECUTE sp_addextendedproperty @name = N'Description', @value = N'This procedure will create a environment variable for each project parameter that has the Required property set to True.
The parameters will then me mapped to the environment variable using the environment reference supplied.', @level0type = N'SCHEMA', @level0name = N'SSIS', @level1type = N'PROCEDURE', @level1name = N'CreateProjectEnvironmentVariables';

